package main

import (
	"fmt"
	
	"net/http"
	"github.com/gin-gonic/gin"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jinzhu/gorm"
    "github.com/thinkerou/favicon"
	
	"strconv"
	"database/sql"
	"log"
)
type class struct{
	Id  int
	Name string
	Local string
	Num int
}
type teacher struct {
	Id        int
	Name      string
	Salary    int
	Subject   string
}
type User struct {
	/* gorm.Model */
	ID int `json:"id"`
	Name      string `gorm:"varchar(20);not null"`
	Telephone string `gorm:"varchar(20);not null;unique"`
	Password  string `gorm:"size:255;not null"`
}
type student struct {
	Id       int
    Name     string
    Sex      string
    Age      int 
    Class    string
    Address  string
}
type cj struct{//成绩结构
	Id int
	Name string
	All int
 }
func MySQLQuerys(db *sql.DB, sqlStr string, id int) []student {
	rows, err := db.Query(sqlStr, id)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	
	students := make([]student, 0)
	for rows.Next() {
		stu1 := student{}
		if err := rows.Scan(&stu1.Id, &stu1.Name, &stu1.Sex, &stu1.Age, &stu1.Class, &stu1.Address); err != nil {
			log.Fatal(err)
		}
		students = append(students, stu1)
	}
	return students
}
func main() {
	//获取初始化的数据库
	db := InitDB()
	//延迟关闭数据库
	defer db.Close() 
	//创建一个默认的路由引擎
	r := gin.Default()
	r.LoadHTMLGlob("templates/*")
    r.Use(favicon.New("templates/favicon.ico"))
	r.GET("/",func(ctx *gin.Context) {
		ctx.HTML(http.StatusOK,"login.html",nil)
	})

	r.GET("/index", Query)
	
	r.GET("/class",ClassQuery)
	//教师静态资源加载
	r.GET("teacher",TeachersQuery)

	r.GET("/jc",jc_Query) 
	r.POST("/jc",func(ctx *gin.Context) {  
		ctx.Redirect(http.StatusFound, "/index")  
	})

	r.GET("/register",func(ctx *gin.Context) {
		ctx.HTML(http.StatusOK,"register.html",nil)
	})

	r.GET("/add",func(ctx *gin.Context) {
		ctx.HTML(http.StatusOK,"add.html",nil)
	})
	r.GET("/delete",func(ctx *gin.Context) {
		ctx.HTML(http.StatusOK,"delete.html",nil)
	})
//r.POST("/jc",jc_Query)
	//添加student
	r.POST("/add", func(ctx *gin.Context) {
		name := ctx.PostForm("name")
		sex := ctx.PostForm("sex")
		ageStr := ctx.PostForm("age")
		ageInt, err := strconv.Atoi(ageStr)
		if err != nil {
			log.Fatal(err)
		}
		class := ctx.PostForm("class")
		address := ctx.PostForm("address")
		//定义了一个接收数据库连接信息的字符串变量 dsn，其中包括用户名、密码、数据库地址和端口等信息
		dsn := "root:123456@tcp(localhost:3306)/db1"

		//使用 sql.Open 方法连接到MySQL数据库
		db, err := sql.Open("mysql", dsn)
		if err != nil {
			log.Fatal(err)
		}

		//准备SQL语句，插入学生信息
		stmt, err := db.Prepare("INSERT INTO students(name, sex, age, class, address) VALUES(?, ?, ?, ?, ?)")
		if err != nil {
			log.Fatal(err)
		}
		defer stmt.Close()
		//执行SQL语句，插入学生信息
		_, err = stmt.Exec(name, sex, ageInt, class, address)
		if err != nil {
			log.Fatal(err)
		}
        
		ctx.Redirect(http.StatusMovedPermanently, "/index")
	})

	//删除
	r.POST("/id_delete", func(ctx *gin.Context) {
		//定义了一个接收数据库连接信息的字符串变量 dsn，其中包括用户名、密码、数据库地址和端口等信息
		dsn := "root:123456@tcp(localhost:3306)/db1"

		//使用 sql.Open 方法连接到MySQL数据库
		db, err := sql.Open("mysql", dsn)

		//如果连接过程中发生错误，则退出程序
		if err != nil {
			log.Fatal(err)
		}
		defer db.Close()

		// 从请求中获取 ID
		id := ctx.PostForm("student_id")
		//如果未获取到学生ID参数，则返回HTTP状态码400和错误消息给客户端。
		if id == "" {
			ctx.String(http.StatusBadRequest, "id is required")
			return
		}
		// 准备一个用于从数据库表 'students' 中删除特定 id 的学生数据的 SQL 语句
		stmt, err := db.Prepare("DELETE FROM students WHERE id = ?")
		if err != nil {
			//记录错误信息
			log.Println(err)
			//使用上下文的 String 方法返回一个 500 内部服务器错误响应给客户端
			ctx.String(http.StatusInternalServerError, "Internal server error")
			return
		}
		defer stmt.Close()

		// 执行删除操作
		// 在数据库中执行准备好的 SQL 语句，删除指定 id 的学生记录
		_, err = stmt.Exec(id)
		if err != nil {
			log.Println(err)
			ctx.String(http.StatusInternalServerError, "Failed to delete record")
			return
		}

		//删除完毕后返回学生页面
		ctx.Redirect(http.StatusFound, "/index")
	})

	r.GET("/display_one_student/:id", func(ctx *gin.Context) {
		id := ctx.Param("id")
		fmt.Println(id)
		ids, err := strconv.Atoi(id)

		if err != nil {
			fmt.Println(err)
        return
		} 
		dsn := "root:root@tcp(localhost:3306)/db1"
		db, err := sql.Open("mysql", dsn)
		if err != nil {
			log.Fatal(err)
		}
		defer db.Close()
	
		// 从请求中获取要查询的学生ID
		// idStr := ctx.PostForm("id") // 使用 PostForm 方法获取表单中的参数
		// // fmt.Println(idStr)
		
		sqlStr := "SELECT * FROM students WHERE id=?"
		
		studentSlice := MySQLQuerys(db, sqlStr, ids)
		fmt.Println(studentSlice)
	
		ctx.HTML(http.StatusOK, "update.html", gin.H{
			"student_slice": studentSlice, // 从数据库中查询到的结果
		})
	})

	//修改
	r.GET("/update/:id", func(ctx *gin.Context) {
		id := ctx.Param("id")
		fmt.Println(id)
		// 查询数据库获取原始数据
		originalData, err := queryOriginalDataFromDB(db, id)
		if err != nil {
		// 处理错误
		}
		// 在 HTML 模板中渲染表单，填充原始数据
		ctx.HTML(http.StatusOK, "update.html", gin.H{
			"id": originalData.Id,
			"name": originalData.Name,
			"sex": originalData.Sex,
			"age": originalData.Age,
			"class": originalData.Class,
			"address": originalData.Address,
		})
	})

	r.POST("/update", func(ctx *gin.Context) {  
		dsn := "root:root@tcp(localhost:3306)/db1"  
		db, err := sql.Open("mysql", dsn)  
		if err != nil {  
			log.Fatal(err)  
		}  
		defer db.Close()  
		// 从请求中获取 ID 和新的 name  

		id := ctx.PostForm("id")  
		newName := ctx.PostForm("name") // 假设请求中包含新的 name 字段
		newSex := ctx.PostForm("sex")
		newAge := ctx.PostForm("age")
		newClass := ctx.PostForm("class")
		newAddress := ctx.PostForm("address")  

		if id == "" || newName == "" || newSex == "" || newAge == "" || newClass == ""|| newAddress == "" {  
			ctx.String(http.StatusBadRequest, "id and name are required")  
			return  
		}  
		stmt, err := db.Prepare("UPDATE students SET name=? ,sex=?, age=?, class=?, address=? WHERE id = ?")  
		if err != nil {  
			log.Println(err)  
			ctx.String(http.StatusInternalServerError, "Internal server error")  
			return  
		}  
		defer stmt.Close()  
	  
		_, err = stmt.Exec(newName, newSex, newAge, newClass, newAddress, id) // 注意参数的顺序  
		if err != nil {  
			log.Println(err)  
			ctx.String(http.StatusInternalServerError, "Failed to update record")  
			return  
		}  
		ctx.Redirect(http.StatusFound, "/index")  
	})

	//注册

	r.POST("/register", func(ctx *gin.Context) {
		//获取参数
		name := ctx.PostForm("name")
		telephone := ctx.PostForm("telephone")
		password := ctx.PostForm("password")

		//数据验证
		if len(name) == 0 {
			ctx.JSON(http.StatusUnprocessableEntity, gin.H{
				"code":    422,
				"message": "用户名不能为空",
			})
			return
		}
		//判断手机号是否存在
		var user User
		db.Where("telephone = ?", telephone).First(&user)
		if user.ID != 0 {
			ctx.JSON(http.StatusUnprocessableEntity, gin.H{
				"code":    422,
				"message": "用户已存在",
			})
			return
		}
		newUser := User{
			Name:      name,
			Telephone: telephone,
			Password:  password,
		}
		db.Create(&newUser)

		ctx.Redirect(http.StatusMovedPermanently,"/")
	})

	//登录
	r.POST("/login", func(ctx *gin.Context) {
			// 获取参数
			name := ctx.PostForm("name")
			password := ctx.PostForm("password")
		
			var user User
			if err := db.Where("name = ?", name).First(&user).Error(); err != nil {
				ctx.JSON(http.StatusUnprocessableEntity, gin.H{
					"code":    422,
					"message": "用户不存在",
				})
				return
			}
		
			if user.Password != password {
				ctx.Redirect(http.StatusMovedPermanently, "/")
				return
			}
		
			// 返回结果
			ctx.Redirect(http.StatusMovedPermanently, "/index")
		

	})
	//在9090端口启动服务
	panic(r.Run(":9090"))
}

func MySQLQuery(db *sql.DB, sqlStr string) []student {
	rows, err := db.Query(sqlStr)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	students := make([]student, 0)
	for rows.Next() {
		stu1 := student{}
		if err := rows.Scan(&stu1.Id, &stu1.Name, &stu1.Sex, &stu1.Age, &stu1.Class,  &stu1.Address); err != nil {
			log.Fatal(err)
		}
		students = append(students, stu1)
	}
	return students
}
func jc_Query(c *gin.Context) {
	dsn := "root:root@tcp(localhost:3306)/db1"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	sqlStr := "SELECT * FROM cj"
	student_jc := MySQLJcQuery(db, sqlStr) 
	fmt.Println(student_jc)
	titleStr := "This is a HelloWorld template"
	c.HTML(http.StatusOK, "jc.html", gin.H{
		"student_slice": student_jc, // 从数据库中查询到的结果
		"title":         titleStr,
	})
}
func MySQLJcQuery(db *sql.DB, sqlStr string) []cj{
	rows, err := db.Query(sqlStr)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	cjs := make([]cj, 0)
	
	for rows.Next() {
		stu1 := cj{}
		if err := rows.Scan(&stu1.Id, &stu1.Name, &stu1.All); err != nil {
			log.Fatal(err)
		}
		cjs = append(cjs, stu1)
	}
	fmt.Println(cjs)
	return cjs
}

func Query(c *gin.Context) {
	dsn := "root:root@tcp(localhost:3306)/db1"
	// "账号:密码@tcp(localhost:端口号)/数据库名"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	sqlStr := "SELECT * FROM students"
	// "SELECT * FROM 表名"
	studentSlice := MySQLQuery(db, sqlStr)
	titleStr := "This is a HelloWorld template"
	c.HTML(http.StatusOK, "index.html", gin.H{
		"student_slice": studentSlice, // 从数据库中查询到的结果
		"title":         titleStr,
	})
}
func queryOriginalDataFromDB(db *gorm.DB, id string) (student, error) {
    var originalData student
    if err := db.Where("id = ?", id).First(&originalData).Error; err != nil {
        return student{}, err
    }
    return originalData, nil
}
func InitDB() *gorm.DB {
	driverName := "mysql"
	host := "127.0.0.1"
	port := "3306"
	database := "db1"
	username := "root"
	password := "root"
	charset := "utf8"
	args := fmt.Sprintf("%s:%s@(%s:%s)/%s?charset=%s&parseTime=true",
		username,
		password,
		host,
		port,
		database,
		charset)

	db, err := gorm.Open(driverName, args)
	if err != nil {
		panic("failed to connect database, err:" + err.Error())
	}
	//迁移
	db.AutoMigrate(&User{})

	return db
}
func MySQLClassQuery(db *sql.DB, sqlStr string) ([]class, error) {  
    rows, err := db.Query(sqlStr)  
    if err != nil {  
        return nil, err  
    }  
    defer rows.Close() // 确保在函数返回前关闭rows  
  
    var classes []class  
    for rows.Next() {  
        var cal class  
        if err := rows.Scan(&cal.Id, &cal.Name, &cal.Local, &cal.Num); err != nil {  
            return nil, err  
        }  
        classes = append(classes, cal)  
    }  
    if err := rows.Err(); err != nil { // 检查rows在迭代过程中是否发生了错误  
        return nil, err  
    }  
    return classes, nil  
}  
  
func ClassQuery(c *gin.Context) {  
    dsn := "root:root@tcp(localhost:3306)/db1"  
    db, err := sql.Open("mysql", dsn)  
    if err != nil {  
        log.Println("Failed to connect to database:", err)  
        c.String(http.StatusInternalServerError, "Failed to connect to database")  
        return  
    }  
    defer db.Close()  
  
    sqlStr := "SELECT * FROM classs"   
    classSlice, err := MySQLClassQuery(db, sqlStr)  
    if err != nil {  
        log.Println("Error querying database:", err)  
        c.String(http.StatusInternalServerError, "Error querying database")  
        return  
    }  
  
    titleStr := "This is a HelloWorld template"  
    c.HTML(http.StatusOK, "class.html", gin.H{  
        "class_slice": classSlice,  
        "title":       titleStr,  
    })  
}

func MySQLTeacherQuery(db *sql.DB, sqlStr string) ([]teacher, error) {  
    rows, err := db.Query(sqlStr)  
    if err != nil {  
        return nil, err  
    }  
    defer rows.Close() // 确保在函数返回前关闭rows  
  
    var teachers []teacher  
    for rows.Next() {  
        var cal teacher  
        if err := rows.Scan(&cal.Id, &cal.Name, &cal.Salary, &cal.Subject); err != nil {  
            return nil, err  
        }  
        teachers = append(teachers, cal)  
    }  
    if err := rows.Err(); err != nil { // 检查rows在迭代过程中是否发生了错误  
        return nil, err  
    }  
    return teachers, nil  
}  

func TeachersQuery(c *gin.Context) {  
    dsn := "root:root@tcp(localhost:3306)/db1"  
    db, err := sql.Open("mysql", dsn)  
    if err != nil {  
        log.Println("Failed to connect to database:", err)  
        c.String(http.StatusInternalServerError, "Failed to connect to database")  
        return  
    }  
    defer db.Close()  
  
    sqlStr := "SELECT * FROM teachers"   
    teacherSlice, err := MySQLTeacherQuery(db, sqlStr)  
    if err != nil {  
        log.Println("Error querying database:", err)  
        c.String(http.StatusInternalServerError, "Error querying database")  
        return  
    }  
  
    titleStr := "This is a HelloWorld template"  
    c.HTML(http.StatusOK, "teacher.html", gin.H{  
        "teacher_slice": teacherSlice,  
        "title":       titleStr,  
    })  
}